package sv.com.seguros.dao.ctg;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.type.Type;

import sv.com.seguros.dao.BaseDaoImpl;
import sv.com.seguros.dto.ctg.CtgAsesor;
import sv.com.seguros.dto.ctg.CtgCatalogo;
import sv.com.seguros.exception.WebException;
import sv.com.seguros.web.util.WebUtilidades;

public class CtgAsesorDaoImpl extends BaseDaoImpl implements CtgAsesorDao{

	@SuppressWarnings("unchecked")
	public List<Object[]> findAllAsArray() throws WebException{
		DetachedCriteria criteria = DetachedCriteria.forClass(CtgAsesor.class);
		criteria.addOrder(Order.asc("ctgAsesorNombreCompleto"));
		criteria.createAlias("ctgAseguradora", "ctgAseguradora", Criteria.LEFT_JOIN);
		criteria.setProjection(Projections.projectionList().
				add(Projections.property("ctgAsesorId")).
				add(Projections.property("ctgAsesorNombreCompleto")).
				add(Projections.property("ctgAsesorDocumento1")).
				add(Projections.property("ctgAsesorDocumento2")).
				add(Projections.property("ctgAsesorCorreo")).
				add(Projections.property("ctgAsesorTelefono1")).
				add(Projections.property("ctgAsesorTelefono2")).
				add(Projections.property("ctgAsesorFechaNacimiento")).
				add(Projections.property("ctgAsesorFechaIngreso")).
				add(Projections.property("ctgAsesorDireccion")).
				add(Projections.property("ctgAsesorActivo")).
				add(Projections.property("ctgAseguradora.ctgAseguradoraId")).
				add(Projections.sqlProjection("COALESCE(ctg_ase_nombre, 'NO APLICA') as ctgAseguradoraNombre", new String[]{"ctgAseguradoraNombre"}, new Type[]{Hibernate.STRING}))
			);
		return (List<Object[]>) findByCriteria(criteria);
	}
	
	@SuppressWarnings("unchecked")
	public List<Object[]> findAllActivesAsArray() throws WebException{
		DetachedCriteria criteria = DetachedCriteria.forClass(CtgAsesor.class);
		criteria.addOrder(Order.asc("ctgAseguradora.ctgAseguradoraId"));
		criteria.add(Restrictions.eq("ctgAsesorActivo", "1"));
		criteria.setProjection(Projections.projectionList().
				add(Projections.property("ctgAsesorId")).
				add(Projections.property("ctgAsesorNombreCompleto")).
				add(Projections.property("ctgAseguradora.ctgAseguradoraId"))
		);
		return (List<Object[]>) findByCriteria(criteria);
	}
	
	@SuppressWarnings("unchecked")
	public List<Object[]> findAllSgdRolByCtgAsesorId(Long ctgAsesorId) throws WebException{
		String hqlQuery = WebUtilidades.getQueryByName("sgdAcceso.findAllSgdRolesByCtgAsesorId");
		try {
			return (List<Object[]>) findByHQLQuery(hqlQuery, new Object[]{ctgAsesorId});
		} catch (WebException e) {
			throw e;
		} catch (Exception e) {
			throw new WebException("Problemas a extraer accesos del usuario");
		}
	}
	
	@SuppressWarnings("unchecked")
	public List<Object> findSgdRolesAsArray() throws WebException{
		DetachedCriteria criteria = DetachedCriteria.forClass(CtgCatalogo.class);
		criteria.add(Restrictions.eq("ctgCatalogoPadre", WebUtilidades.SGD_ROLES_PADRE));
		criteria.add(Restrictions.eq("ctgCatalogoActivo", "1"));
		criteria.setProjection(Projections.projectionList().
			add(Projections.property("ctgCatalogoId")).
			add(Projections.property("ctgCatalogoNombre")).
			add(Projections.sqlProjection("'0' as asignado", new String[]{"asignado"}, new Type[]{Hibernate.STRING}))
		);
		try {
			return (List<Object>) findByCriteria(criteria);
		} catch (Exception e) {
			throw new WebException("Problemas a tratar de obtener los accesos generales");
		}
	}
	
	public void toggleActiveUser(Long ctgAsesorId, String ctgAsesorActivo) throws WebException{
		Map<String, Object> parameters = new HashMap<String, Object>();
		parameters.put("ctgAsesorId", ctgAsesorId);
		parameters.put("ctgAsesorActivo", "1".equals(ctgAsesorActivo) ? "0" : "1");
		String hqlQuery = "update CtgAsesor set ctgAsesorActivo = :ctgAsesorActivo where ctgAsesorId = :ctgAsesorId";
		try {
			executeNonHibernateQuery(hqlQuery, parameters);
		} catch (Exception e) {
			throw new WebException("Problemas al actualizar la informaci\u00F3n");
		}
	}
	
	public void deleteCtgAsesor(Long ctgAsesorId) throws WebException{
		try{
			Map<String, Object> parameters = new HashMap<String, Object>();
			parameters.put("ctgAsesorId", ctgAsesorId);
			String hqlQuery = "delete from CtgAsesor where ctgAsesorId = :ctgAsesorId";
			executeNonHibernateQuery(hqlQuery, parameters);
		}catch(Exception e){
			throw new WebException("Problemas al tratar de eliminar el registro <br />Registro puede estar asociado a un Cliente, Poliza, etc");
		}
	}
	
}
